﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace WebApplication1
{
    public class DbHelper
    {
        private static string _connString;

        static DbHelper()
        {
            // 修正配置文件路径（去掉开头斜杠，使用相对路径）
            var configuration = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .Build();

            _connString = configuration.GetConnectionString("SqlServer");
        }

        // 修正：移除using，避免连接被提前释放
        private static SqlConnection CreateConnection()
        {
            SqlConnection connection = new SqlConnection(_connString);
            connection.Open();
            return connection;
        }

        // 修正：移除using，避免命令被提前释放
        private static SqlCommand PrepareCommand(SqlConnection conn, string sql, params SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return cmd;
        }

        /// <summary>
        /// 增删改操作
        /// </summary>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            // 在这里用using管理连接生命周期（确保使用后释放）
            using SqlConnection conn = CreateConnection();
            using SqlCommand cmd = PrepareCommand(conn, sql, parameters);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 获取首行首列（处理可能的null值）
        /// </summary>
        public static int ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using SqlConnection conn = CreateConnection();
            using SqlCommand cmd = PrepareCommand(conn, sql, parameters);
            var result = cmd.ExecuteScalar();
            // 修正：处理DBNull或null的情况，避免转换异常
            return result == DBNull.Value || result == null ? 0 : Convert.ToInt32(result);
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        public static DataTable GetDataTable(string sql, params SqlParameter[] parameters)
        {
            using SqlDataAdapter adapter = new SqlDataAdapter(sql, _connString);
            if (parameters != null && parameters.Length > 0)
            {
                // 修正：移除多余的ToArray()
                adapter.SelectCommand.Parameters.AddRange(parameters);
            }
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
    }
}
